#### USE THIS
## (EYB - BYB)/BYB * 100
## Rose has notes on her choice for this calculation
pct_change_ds <- endowment_data %>%
  filter(!is.na(BeginningYearBalanceAmt)) %>%
  mutate(change = EndYearBalanceAmt - BeginningYearBalanceAmt,
         pct_change = change/BeginningYearBalanceAmt * 100) %>%
  arrange(desc(pct_change)) %>%
  left_join(names, by = "EIN")

#### USE THIS
## (EYB - INV - BYB)/BYB * 100
## Rose has notes on her choice for this calculation
pct_change_inv_ds <- endowment_data %>%
  filter(!is.na(BeginningYearBalanceAmt)) %>%
  mutate(InvestmentEarningsOrLossesAmt = ifelse(is.na(InvestmentEarningsOrLossesAmt), 0, InvestmentEarningsOrLossesAmt)) %>%
  mutate(change = EndYearBalanceAmt - InvestmentEarningsOrLossesAmt - BeginningYearBalanceAmt,
         pct_change = change/BeginningYearBalanceAmt * 100) %>%
  arrange(desc(pct_change)) %>%
  left_join(names, by = "EIN")

# Basic summary stats
pct_change_ds %>%   
  filter(!is.na(pct_change) & pct_change != Inf) %>%
  summarize(avg_pct_change = mean(pct_change),
            median_pct_change = median(pct_change),
            sd_pct_change = sd(pct_change))

pct_change_ds %>%   
  filter(!is.na(pct_change) & pct_change != Inf) %>%
  group_by(EIN) %>%
  summarize(avg_pct_change = mean(pct_change),
            median_pct_change = median(pct_change),
            sd_pct_change = sd(pct_change))

# Basic histogram summarizing it
ggplot(pct_change_ds, aes(x = pct_change)) +
  geom_histogram(binwidth = 20) + 
  xlab("% Change\n(EYB - BYB) / BYB * 100") + 
  ggtitle(label = "Percentage of Change in Endowment Balance", subtitle = "Red Line indicates 100%") +
  theme_classic() + 
  geom_vline(xintercept = 100, color = "maroon", linetype = "dotted")

## Histogram with cutoff of 200%
ggplot(pct_change_ds, aes(x = pct_change)) +
  geom_histogram(binwidth = 5) + 
  xlab("% Change\n(EYB - BYB) / BYB * 100") + 
  xlim(-100, 200) + 
  ggtitle(label = "Percentage of Change in Endowment Balance") +
  theme_classic()

pct_change_ds %>%
  filter(pct_change != Inf) %>%
  select(organization_name, fiscal_year, pct_change) %>%
  make_table(title = "Percentage of Change in Endowment Balance", col_names = c("Name", "Fiscal Year", "% Change")) %>%
  scroll_box(height = "450px")

pct_change_ds %>%
  filter(pct_change != Inf) %>%
  select(organization_name, fiscal_year, pct_change) %>%
  group_by(fiscal_year) %>%
  summarize(total = n(),
            avg = mean(pct_change),
            med = median(pct_change),
            sd = sd(pct_change)) %>%
  make_table(title = "Percentage of Change in Endowment Balance By Year", col_names = c("Fiscal Year", "Total Companies", "Average % Change", "Median % Change", "Standard Deviation")) %>%
  scroll_box(height = "450px")

# Basic summary stats
pct_change_inv_ds %>%   
  filter(!is.na(pct_change) & pct_change != Inf) %>%
  summarize(avg_pct_change = mean(pct_change),
            median_pct_change = median(pct_change),
            sd_pct_change = sd(pct_change))

pct_change_inv_ds %>%   
  filter(!is.na(pct_change) & pct_change != Inf) %>%
  group_by(EIN) %>%
  summarize(avg_pct_change = mean(pct_change),
            median_pct_change = median(pct_change),
            sd_pct_change = sd(pct_change))

# Basic histogram summarizing it
ggplot(pct_change_inv_ds, aes(x = pct_change)) +
  geom_histogram(binwidth = 20) + 
  xlab("% Change\n(EYB - INV - BYB) / BYB * 100") + 
  ggtitle(label = "Percentage of Change in Endowment Balance", subtitle = "Red Line indicates 100%") +
  theme_classic() + 
  geom_vline(xintercept = 100, color = "maroon", linetype = "dotted")

## Histogram with cutoff of 200%
ggplot(pct_change_inv_ds, aes(x = pct_change)) +
  geom_histogram(binwidth = 5) + 
  xlab("% Change\n(EYB - BYB) / BYB * 100") + 
  xlim(-100, 200) + 
  ggtitle(label = "Percentage of Change in Endowment Balance") +
  theme_classic()

pct_change_inv_ds %>%
  filter(pct_change != Inf) %>%
  select(organization_name, fiscal_year, pct_change) %>%
  make_table(title = "Percentage of Change in Endowment Balance", col_names = c("Name", "Fiscal Year", "% Change")) %>%
  scroll_box(height = "450px")

pct_change_inv_ds %>%
  filter(pct_change != Inf) %>%
  select(organization_name, fiscal_year, pct_change) %>%
  group_by(fiscal_year) %>%
  summarize(total = n(),
            avg = mean(pct_change),
            med = median(pct_change),
            sd = sd(pct_change)) %>%
  make_table(title = "Percentage of Change in Endowment Balance By Year", col_names = c("Fiscal Year", "Total Companies", "Average % Change", "Median % Change", "Standard Deviation")) %>%
  scroll_box(height = "450px")

## Ranges of different percent change  
# Reordering by standard deviation of pct_spend down
pct_change_ds_box <- pct_change_ds %>%
  group_by(organization_name) %>%
  filter(pct_change != Inf) %>%
  summarize(sd = sd(pct_change, na.rm = TRUE)) %>%
  right_join(pct_change_ds, by = "organization_name") %>%
  select(organization_name, EIN, pct_change, sd) %>%
  mutate(organization_name = reorder(organization_name, -sd, na.rm = TRUE))

## Unlimited 
box_plot <- ggplot(pct_change_ds_box, aes(x = organization_name, y = pct_change)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment Percent Change per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  geom_hline(yintercept = 100, linetype = "dotted", color = "maroon")
ggplotly(box_plot) %>%
  layout(showlegend = FALSE)

##Limited to 100 for visibility 
box_plot_lim <- ggplot(pct_change_ds_box, aes(x = organization_name, y = pct_change)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment % Change (Max of 100) per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  scale_y_continuous(breaks = scales::breaks_pretty(n = 20),
                     limit = c(-100,100))
ggplotly(box_plot_lim)  %>%
  layout(showlegend = FALSE)

## Ranges of different percent change  
# reorder(organization_name, pull(summarize(spend_down, sd = sd(group_by(spend_down, pct_change)))), na.rm = TRUE)
# Reordering by standard deviation of pct_spend down
pct_change_inv_box <- pct_change_inv_ds %>%
  group_by(organization_name) %>%
  filter(pct_change != Inf) %>%
  summarize(sd = sd(pct_change, na.rm = TRUE)) %>%
  right_join(pct_change_ds, by = "organization_name") %>%
  select(organization_name, EIN, pct_change, sd) %>%
  mutate(organization_name = reorder(organization_name, -sd, na.rm = TRUE))

## Unlimited 
box_plot_inv <- ggplot(pct_change_inv_box, aes(x = organization_name, y = pct_change)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment Percent Change per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  geom_hline(yintercept = 100, linetype = "dotted", color = "maroon")
ggplotly(box_plot_inv) %>%
  layout(showlegend = FALSE)

##Limited to 100 for visibility 
box_plot_inv_lim <- ggplot(pct_change_inv_box, aes(x = organization_name, y = pct_change)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment % Change (Max of 100) per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  scale_y_continuous(breaks = scales::breaks_pretty(n = 20),
                     limit = c(-100,100))
ggplotly(box_plot_inv_lim)  %>%
  layout(showlegend = FALSE)

## Retrieving median values  
pct_change_med <- pct_change_ds %>%
  group_by(fiscal_year) %>%
  summarize(median = median(pct_change, na.rm = TRUE)) %>%
  mutate(organization_name = "Median")

## Spend Down over Time
pct_change_plot <- pct_change_ds %>%
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray") +
  geom_point(data = pct_change_med, aes(x = fiscal_year, y = median), color = "black", size = 1)
ggplotly(pct_change_plot) 

##Plot with Y scale between -100 and 100 
limited_scale <- pct_change_ds %>%
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance (max 100)",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20),
                     limits = c(-100, 100))  +
  geom_point(data = pct_change_med, aes(x = fiscal_year, y = median), color = "black", size = 1)
ggplotly(limited_scale)

## Retrieving median values  
pct_change_inv_med <- pct_change_inv_ds %>%
  group_by(fiscal_year) %>%
  summarize(median = median(pct_change, na.rm = TRUE)) %>%
  mutate(organization_name = "Median")

## Spend Down over Time
pct_change_inv_plot <- pct_change_inv_ds %>%
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")  +
  geom_point(data = pct_change_inv_med, aes(x = fiscal_year, y = median), color = "black", size = 1)
ggplotly(pct_change_inv_plot) 

##Plot with Y scale between -100 and 100 
limited_scale_inv <- pct_change_inv_ds %>%
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance (max 100)",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20),
                     limits = c(-100, 100))  +
  geom_point(data = pct_change_inv_med, aes(x = fiscal_year, y = median), color = "black", size = 0.5)
ggplotly(limited_scale_inv)

## Pandemic Years
pct_change_plot <- pct_change_ds %>%
  filter(fiscal_year %in% c("2018", "2019", "2020", "2021", "2022")) %>% 
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "Within Pandemic Years") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")
ggplotly(pct_change_plot) 

## Table of available in-pandemic data 
pct_change_ds %>%
  filter(fiscal_year %in% c("2019", "2020", "2021", "2022")) %>%
  select(organization_name, pct_change, fiscal_year) %>%
  arrange(desc(fiscal_year)) %>%
  make_table(title = "Percentage of Change in Endowment Balance within Pandemic Years", col_names = c("Name", "% Change", "Year")) %>%
  scroll_box(height = "450px")

pct_change_ds %>%
  filter(fiscal_year %in% c("2019", "2020", "2021", "2022")) %>%
  select(organization_name, pct_change, fiscal_year) %>%
  group_by(fiscal_year) %>%
  summarize(total_in_year = n())

## Pandemic Years
pct_change_plot_inv <- pct_change_inv_ds %>%
  filter(fiscal_year %in% c("2018", "2019", "2020", "2021", "2022")) %>% 
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "Within Pandemic Years") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")
ggplotly(pct_change_plot_inv) 

## Table of available in-pandemic data 
pct_change_inv_ds %>%
  filter(fiscal_year %in% c("2019", "2020", "2021", "2022")) %>%
  select(organization_name, pct_change, fiscal_year) %>%
  arrange(desc(fiscal_year)) %>%
  make_table(title = "Percentage of Change in Endowment Balance within Pandemic Years", col_names = c("Name", "% Change", "Year")) %>%
  scroll_box(height = "450px")

pct_change_inv_ds %>%
  filter(fiscal_year %in% c("2019", "2020", "2021", "2022")) %>%
  select(organization_name, pct_change, fiscal_year) %>%
  group_by(fiscal_year) %>%
  summarize(total_in_year = n())